package dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import model.ManagerInfo;
import model.UserCommentInfo;
import model.UserInfo;

public class UserDao {
	public boolean checkRegister(String userName,String passWord) throws SQLException{
		ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
		String sql = "select * from user_info where user_name="+"'"+userName+"'";
		UserInfo userInfo = queryRunner.query(sql, new BeanHandler<UserInfo>(UserInfo.class));
		if(userInfo== null) {
			String insertSql = "insert into user_info(user_name,user_password,user_reputation) values(?,?,0)";
			int row = queryRunner.update(insertSql,userName,passWord);
			if(row>0) {
				return true;
			}else {
				return false;
			}
		}else {
			return false;
		}
	}
	public boolean checkLogin(String userName,String passWord) throws SQLException{
		ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql = "select * from user_info where user_name ="+"'"+userName+"'"+" and user_password ="+"'"+passWord+"'";
        UserInfo userInfo = queryRunner.query(sql, new BeanHandler<UserInfo>(UserInfo.class));
        if(null != userInfo) {
        	return true;
        }else {
        	return false;
        }
		
	}
	public boolean checkManagerLogin(String managerName,String passWord) throws SQLException{
		ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql = "select * from manager_info where manager_name="+"'"+managerName+"'"+" and manager_password = "+"'"+passWord+"'";
        ManagerInfo managerInfo = queryRunner.query(sql, new BeanHandler<ManagerInfo>(ManagerInfo.class));
        if(null != managerInfo) {
        	return true;
        }else {
        	return false;
        }
	}
	public boolean addUserComment(String userName, String comment,String phoneName) throws SQLException{
		ComboPooledDataSource dataSource = new ComboPooledDataSource();
		QueryRunner queryRunner = new QueryRunner(dataSource);
		String sql = "select user_name from user_info where user_name ="+"'"+userName+"'";
		UserCommentInfo userCommentInfo = queryRunner.query(sql, new BeanHandler<UserCommentInfo>(UserCommentInfo.class));
		if(null != userCommentInfo) {
			String insertSql = "insert into user_comment_info(user_name,user_comment_content,phone_name) values(?,?,?)";
			int row = queryRunner.update(insertSql,userName,comment,phoneName);
			if(row>0) {
				return true;
			}else {
				return false;
			}
		}else {
			return false;
		}
	}
	public List<UserCommentInfo> getUserCommentList(String phoneName) throws SQLException {
		ComboPooledDataSource dataSource = new ComboPooledDataSource();
		QueryRunner queryRunner = new QueryRunner(dataSource);
		String sql = "select user_name,user_comment_content from user_comment_info where phone_name ="+"'"+phoneName+"'";
		List<UserCommentInfo> commentList = queryRunner.query(sql, new BeanListHandler<UserCommentInfo>(UserCommentInfo.class));
		return commentList;
	}
	
	/*
	 * 以下代码皆为后台管理代码
	 */
	
	public List<UserCommentInfo> getCommentInfosList() throws SQLException {
		ComboPooledDataSource dataSource = new ComboPooledDataSource();
		QueryRunner queryRunner = new QueryRunner(dataSource);
		String sql = "select * from user_comment_info";
		List<UserCommentInfo> commentInfosList = queryRunner.query(sql, new BeanListHandler<UserCommentInfo>(UserCommentInfo.class));
		return commentInfosList;
	}
	public boolean deleteComment(int userId) throws SQLException {
		ComboPooledDataSource dataSource = new ComboPooledDataSource();
		QueryRunner queryRunner = new QueryRunner(dataSource);
		String sql = "delete from user_comment_info where user_id="+userId;
		int row = queryRunner.update(sql);
		if(row>0) {
			return true;
		}else {
			return false;
		}
	}
	public boolean updateUserReputation(String  userName) throws SQLException {
		ComboPooledDataSource dataSource = new ComboPooledDataSource();
		QueryRunner queryRunner = new QueryRunner(dataSource);
		String sql = "update user_info set user_reputation=user_reputation+1 WHERE user_name="+"'"+userName+"'";
		int row = queryRunner.update(sql);
		if(row>0) {
			return true;
		}else {
			return false;
		}
	}
	public boolean updateUserJoinActivity(String userName,String activityName) throws SQLException {
		//插入之前，查询是否为空，若为空，插入失败。
		ComboPooledDataSource dataSource = new ComboPooledDataSource();
		QueryRunner queryRunner = new QueryRunner(dataSource);
		String sql="select user_join_activity from user_info where user_name="+"'"+userName+"'";
		UserInfo userInfo = queryRunner.query(sql, new BeanHandler<UserInfo>(UserInfo.class));
		if(null==userInfo.getUser_join_activity()) {
			//若没查询到，开始插入
			String insertSql= "update user_info set user_join_activity=? where user_name=?";
			int row=queryRunner.update(insertSql,activityName,userName);
			if(row>0) {
				return true;
			}else {
				return false;
			}
		}else {
			return false;
		}
	}
	public int checkUserReputation(String userName) throws SQLException {
		ComboPooledDataSource dataSource = new ComboPooledDataSource();
		QueryRunner queryRunner = new QueryRunner(dataSource);
		String sql="select user_reputation from user_info where user_name="+"'"+userName+"'";
		UserInfo userInfo = queryRunner.query(sql, new BeanHandler<UserInfo>(UserInfo.class));
		return userInfo.getUser_reputation();
	}
	public List<UserInfo> checkJoinActivityUsers() throws SQLException {
		ComboPooledDataSource dataSource = new ComboPooledDataSource();
		QueryRunner queryRunner = new QueryRunner(dataSource);
		String sql = "select * from user_info where user_join_activity is not null";
		List<UserInfo> joinActivityUsersList = queryRunner.query(sql, new BeanListHandler<UserInfo>(UserInfo.class));
		return joinActivityUsersList;
	}

}
